﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Expense.Tracker 
{
    public class ExpenseDataQuery
    {
        #region "Declaration"
        ExpenseTracker _tracker;
        #endregion

        #region "Public"
        public string ReturnQueryString(string qryType, ExpenseTracker tracker)
        {
            string query = "";

            _tracker = tracker;

            if (qryType == "loadtree")
                query = ReturnLoadTreeQueryString();
            else if (qryType == "loaddisplay")
                query = ReturnLoadDisplayQueryString();
            else if (qryType == "loadgraph")
                query = ReturnLoadGraphQueryString();
            else if (qryType == "insert")
                query = ReturnInsertQueryString();
            else if (qryType == "settings")
                query = ReturnLoadSettingsQueryString();
            else if (qryType == "savesettings")
                query = ReturnUpdateSettingsQueryString();
            else if (qryType == "types")
                query = ReturnLoadTypeQueryString();
            else if (qryType == "savetypes")
                query = ReturnInsertTypeQueryString();
            return query;
        }

        public Dictionary<string, string> ReturnQueryDictionary(string qryType, ExpenseTracker tracker)
        {
            Dictionary<string, string> queryDict;

            _tracker = tracker;

            try
            {
                queryDict = new Dictionary<string, string>();

                if (qryType == "insert")
                {
                    queryDict.Add("@ExpenseType", _tracker.ExpenseType);
                    queryDict.Add("@ExpenseDate", _tracker.ExpenseDate.ToShortDateString());
                    queryDict.Add("@ExpenseReceipt", _tracker.ExpenseReceipt);
                    queryDict.Add("@ExpenseValue", _tracker.ExpenseValue.ToString());
                }
                else if (qryType == "loaddisplay")
                {
                    queryDict.Add("@ExpenseDate", _tracker.ExpenseDate.ToShortDateString());
                }
                else if (qryType == "savesettings")
                {
                    queryDict.Add("@SettingsValue", _tracker.SettingsValue.ToString());
                    queryDict.Add("@SettingsID", _tracker.SettingsID.ToString());
                }
                else if (qryType == "savetypes")
                {
                    queryDict.Add("@TypeValue", _tracker.ExpenseType);
                }
                else if (qryType == "loadgraph")
                {
                    queryDict.Add("@MonthStart", _tracker.ExpenseType);
                    queryDict.Add("@MonthEnd", _tracker.ExpenseReceipt);
                }
                else
                {
                    queryDict = null;
                }
            }
            catch (ArgumentException)
            {
                throw;
            }
            
            return queryDict;
        }
        #endregion

        #region "Private"

        private string ReturnInsertQueryString()
        {
            StringBuilder query = new StringBuilder();

            query.Append("INSERT INTO tblExpenses");
            query.Append("(ExpenseType ");
            query.Append(",ExpenseDate ");
            query.Append(",ExpenseReceipt ");
            query.Append(",ExpenseValue)");
            query.Append("VALUES ");
            query.Append("(@ExpenseType ");
            query.Append(",@ExpenseDate ");
            query.Append(",@ExpenseReceipt ");
            query.Append(",@ExpenseValue)");

            return query.ToString();
        }

        private string ReturnLoadTreeQueryString()
        {
            StringBuilder query = new StringBuilder();
            query.Append("SELECT DISTINCT ExpenseDate ");
            query.Append("FROM tblExpenses ");
            query.Append("WHERE Purge = 'N' ");

            return query.ToString();
        }

        private string ReturnLoadDisplayQueryString()
        {
            StringBuilder query = new StringBuilder();
            query.Append("SELECT ExpenseType, ");
            query.Append("       ExpenseDate, ");
            query.Append("       ExpenseReceipt, ");
            query.Append("       ExpenseValue ");
            query.Append("FROM tblExpenses ");
            query.Append("WHERE Purge = 'N' ");
            query.Append("AND ExpenseDate = @ExpenseDate ");

            return query.ToString();
        }

        private string ReturnLoadGraphQueryString()
        {
            StringBuilder query = new StringBuilder();
            query.Append("SELECT ExpenseDate, ");
            query.Append("       SUM(ExpenseValue) AS ExpenseSum ");
            query.Append("FROM tblExpenses ");
            query.Append("WHERE Purge = 'N' ");
            query.Append("GROUP BY ExpenseDate ");
            query.Append("HAVING (ExpenseDate >= CONVERT(datetime, @MonthStart)) ");
            query.Append("AND (ExpenseDate <= CONVERT(datetime, @MonthEnd)) ");

            return query.ToString();
        }

        private string ReturnLoadSettingsQueryString()
        {
            return "SELECT * FROM tblSettings";
        }

        private string ReturnUpdateSettingsQueryString()
        {
            StringBuilder query = new StringBuilder();

            query.Append("UPDATE tblSettings ");
            query.Append("SET SettingsValue = @SettingsValue ");
            query.Append("WHERE SettingsID = @SettingsID ");

            return query.ToString();
        }

        private string ReturnLoadTypeQueryString()
        {
            StringBuilder query = new StringBuilder();
            query.Append("SELECT TypeDesc ");
            query.Append("FROM tblTypes ");
            query.Append("WHERE Purge = 'N' ");

            return query.ToString();
        }

        private string ReturnInsertTypeQueryString()
        {
            StringBuilder query = new StringBuilder();

            query.Append("INSERT INTO tblTypes");
            query.Append("(TypeDesc) ");
            query.Append("VALUES ");
            query.Append("(@TypeValue) ");

            return query.ToString();
        }

        #endregion
    }
}
